Software Vault: The Gold Collection/Software Vault - The Gold Collection (American Databankers) (1993).ISO/cdr08/sqledit.zip/SQLEDIT.INF

OS/2 Help File | 1993-06-04 | 8KB | 154 lines

ΓòÉΓòÉΓòÉ 1. Introduction ΓòÉΓòÉΓòÉ


                Copyright 1993 Ammonoosuc Technology, Inc.

  SQLEdit allows the user to enter an arbitrary SQL SELECT query, run an
existing stored query, edit and run an existing stored query, or execute a
query stored in a file. The output of this query is displayed in a window on
the screen. The user may then choose to INSERT a new row using an existing
output row as a starting point and editing it to create the new row. (S)he may
also choose to DELETE in the base table a row which is being displayed. As a
third choice, (s)he may UPDATE selected columns in an existing output row.

  When editing of the data being displayed has been completed, the user then
tells the program to actually perform the data base modifications. Each
modified or new row becomes the subject of a separate SQL INSERT, UPDATE, or
DELETE transaction. Some (or all) of the transactions may fail if they are
invalid. Reasons for invalidity are manifold. They may include lack of
authorization, modifying a table which is actually a read-only view, etc. In
any case, for every failing transaction, a suitable SQL error message will be
displayed.


ΓòÉΓòÉΓòÉ 2. Data Base Selection ΓòÉΓòÉΓòÉ

  Upon activation, the program will display a list of existing data bases. The
user must select one of them which will become the active database for the
current session. Selection may be accomplished either by selecting an entry and
then pressing the ENTER push button, or by double clicking on the desired
entry. It is assumed that the user is familiar with the contents of each of the
data bases and knows which (s)he wants to edit. No browsing facilities are
included with this program. If there is only a single data base, this selection
process will be bypassed.


ΓòÉΓòÉΓòÉ 3. Query Selection and Composition ΓòÉΓòÉΓòÉ

  At this point, the user may select from a menu bar either to execute an ad
hoc (COMPOSED) SQL query or to execute or edit an existing stored query. This
query may be stored either in the database or in an external file. If (s)he
chooses to compose an ad hoc query, (s)he will be presented with a window in
which the query may be composed. When (s)he is done composing the query, (s)he
should press the ENTER button and the query will be executed. If the user
chooses to work with a stored query and there are no stored queries, the stored
query section of the menu bar will be disabled and (s)he must choose to either
compose a query or select a file containing a query. If the user chooses to
work with a query stored in the database and there is more than one stored
query in the database, a list of all the stored queries in the database will be
presented. The user must choose one of them. Selection may be accomplished
either by selecting an entry and then pressing the ENTER push button, or by
double clicking on the desired entry. If there is only a single stored query in
the data base, this selection is bypassed. If the user has elected to edit an
existing stored query, the text of the stored query will be displayed in an
edit window where it may be modified. When editing is completed, the user
should press the ENTER button and the edited query will be executed. Otherwise,
if editing is not invoked, an existing stored query will be directly executed.
If the user specifies a query stored in an external file, it is assumed that
this file is a text file containing a single query. If this option is selected,
a file selection box will be presented to the user. When a file has been
selected, it will be read and the contents executed as a query. The text in
this file will be completely unedited when it is executed as a query.


ΓòÉΓòÉΓòÉ 4. Query Execution and Data Presentation ΓòÉΓòÉΓòÉ

  When the SQL query is executed, if execution is unsuccessful, a standard SQL
error message will be displayed explaining why the query is in error. A further
explanation of the error may be found in the normal SQL error message manual.
At this point, the query selection process will restart. Otherwise, the output
of the query will be displayed in a window. The format of this output display
depends only on the stored data and is independent of any stored forms or other
external formatting information. The order in which columns are displayed
depends only on their order in the query. All data types may be displayed in
this window except LONG VARCHAR. Queries that return LONG VARCHAR data are not
supported. This window may be scrolled both horizontally and vertically if
necessary to display all the data. This window is fixed in size and may not be
resized.


ΓòÉΓòÉΓòÉ 5. Creating INSERT Transactions ΓòÉΓòÉΓòÉ

  The philosophy behind executing an INSERT transaction is to choose an
existing row as a model for the new row, duplicate it, and edit it until we
have the row we want to insert, keeping th number of columns the same. To
accomplish this , single click on the INSERT button using mouse button 1. Then
position the cursor anywhere on the row to be duplicated and single click using
mouse button 2. This will cause a copy of the selected row to be appended to
the end of the display. This appended row will be displayed in the same color
as the INSERT button. Note that this is independent of whether the underlying
table permits duplicate rows or not. This duplicate row may now be edited by
positioning the cursor on this row and single clicking the SELECT push button
using mouse button 1. Now position the cursor on the data item in the row to be
edited and press the RIGHT mouse button. This will cause the selected data item
to be displayed in a separate window where it may be edited. This procedure may
be repeated for as many data items within the row as required. Note that
editing character data does not require that the data be enclosed in single
quotes. The only time quotes should be used for character data is to include
leading or trailing blanks.


ΓòÉΓòÉΓòÉ 6. Creating DELETE Transactions ΓòÉΓòÉΓòÉ

  In order to prepare for a DELETE transaction, single click the DELETE push
button using mouse button 1. Next position the cursor anywhere in the row to be
deleted and single click with mouse button 2. This row is now marked for
deletion and may not be further manipulated. It will be displayed in the same
color as the DELETE push button.


ΓòÉΓòÉΓòÉ 7. Creating UPDATE Transactions ΓòÉΓòÉΓòÉ

  To prepare for an UPDATE transaction, single click the UPDATE push button
using mouse button 1. Next position the cursor on a specific data item to be
updated and single click with mouse button 2. The selected data item will now
be displayed in a separate window where it may be edited. The selected row will
also be displayed in the same color as the UPDATE push button. This procedure
may be repeated as often as desired on a single row in order to update the data
in multiple columns.


ΓòÉΓòÉΓòÉ 8. Committing the edited data ΓòÉΓòÉΓòÉ

  All the above activity simply  prepares data for the appropriate SQL
transaction.  To actually execute  the SQL transactions, click on the EXECUTE
push button. At this point, a new window will appear and the user will be
requested to enter the name of the table against which all the INSERT, UPDATE,
and DELETE transactions will be executed. This table name is totally
independent of the table name (or names) specified in the original SQL query.
Note that this feature gives the user a limited copy capability by executing
INSERT transactions against a different table.

  After the table name has been entered, separate transactions will be executed
for each designated row. If these transactions are successful, each one is
individually followed immediately by a COMMIT transaction. If the transaction
is unsuccessful, an appropriate SQL error message will be displayed. Processing
will continue with the next row after the error message has been acknowledged..

  Once all the transactions have completed, the program returns to the main
menu where the user is given the option of either terminating the program or
executing a new SQL SELECT query.


ΓòÉΓòÉΓòÉ 9. Locking the data base ΓòÉΓòÉΓòÉ

  The program accepts a single command line parameter. This parameter controls
the isolation level of the database. This command line option consists of
either a / or a - character. It is immediately followed by the letter L (case
unimportant). This is immediately followed by the character 1, 2, or 3. 1
specifies Uncommitted Read isolation. 2 specifies Cursor Stability isolation
and is the default. 3 specifies Repeatable Read isolation. If an invalid value
of this parameter is specified, it is simply ignored and the default value is
used. For a discussion of the significance of these terms, consult the OS/2 DBM
documentation.